package com.xky.ui.module.salesManagement.select;

import com.xky.pojo.SalesOrders;
import com.xky.utils.DBUtils;

import javax.swing.JOptionPane;
import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 销售订单查询工具类
 * 提供基本查询和高级查询功能
 */
public class SelectSalesOrder {
    
    private static boolean searchMode = false;
    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");
    
    /**
     * 重置搜索状态
     */
    public static void resetSearchStatus() {
        searchMode = false;
    }

    /**
     * 查询所有销售订单
     * @return 销售订单列表
     */
    public static List<SalesOrders> selectAll() {
        String sql = "SELECT * FROM sales_orders ORDER BY order_id DESC";
        return executeQuery(sql, null);
    }

    /**
     * 根据销售订单ID查询
     * @param id 销售订单ID
     * @return 销售订单
     */
    public static List<SalesOrders> selectById(Integer id) {
        searchMode = true;
        String sql = "SELECT * FROM sales_orders WHERE order_id = ?";
        return executeQuery(sql, preparedStatement -> {
            preparedStatement.setInt(1, id);
        });
    }

    /**
     * 根据订单编号查询
     * @param orderNumber 订单编号
     * @return 销售订单列表
     */
    public static List<SalesOrders> selectByOrderNumber(String orderNumber) {
        searchMode = true;
        String sql = "SELECT * FROM sales_orders WHERE order_number = ?";
        return executeQuery(sql, preparedStatement -> {
            preparedStatement.setString(1, orderNumber);
        });
    }
    
    /**
     * 根据客户ID查询
     * @param customerId 客户ID
     * @return 销售订单列表
     */
    public static List<SalesOrders> selectByCustomerId(Integer customerId) {
        searchMode = true;
        String sql = "SELECT * FROM sales_orders WHERE customer_id = ?";
        return executeQuery(sql, preparedStatement -> {
            preparedStatement.setInt(1, customerId);
        });
    }
    
    /**
     * 按状态查询销售订单
     * @param status 销售订单状态
     * @return 销售订单列表
     */
    public static List<SalesOrders> selectByStatus(String status) {
        searchMode = true;
        String sql = "SELECT * FROM sales_orders WHERE order_status = ?";
        return executeQuery(sql, preparedStatement -> {
            preparedStatement.setString(1, status);
        });
    }
    
    /**
     * 按日期范围查询销售订单
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return 销售订单列表
     */
    public static List<SalesOrders> selectByDateRange(LocalDateTime startDate, LocalDateTime endDate) {
        searchMode = true;
        String sql = "SELECT * FROM sales_orders WHERE order_date BETWEEN ? AND ?";
        return executeQuery(sql, preparedStatement -> {
            preparedStatement.setTimestamp(1, Timestamp.valueOf(startDate));
            preparedStatement.setTimestamp(2, Timestamp.valueOf(endDate));
        });
    }
    
    /**
     * 关键字搜索销售订单
     * @param keyword 关键字
     * @return 销售订单列表
     */
    public static List<SalesOrders> searchByKeyword(String keyword) {
        searchMode = true;
        String sql = "SELECT * FROM sales_orders WHERE order_number LIKE ? OR notes LIKE ?";
        return executeQuery(sql, preparedStatement -> {
            String searchPattern = "%" + keyword + "%";
            preparedStatement.setString(1, searchPattern);
            preparedStatement.setString(2, searchPattern);
        });
    }

    /**
     * 高级查询（使用Map条件）
     * @param conditions 查询条件Map
     * @return 销售订单列表
     */
    public static List<SalesOrders> advancedSearchWithMap(Map<String, Object> conditions) {
        searchMode = true;
        StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM sales_orders WHERE 1=1");
        List<Object> params = new ArrayList<>();

        // 处理订单ID
        if (conditions.containsKey("orderId")) {
            sqlBuilder.append(" AND order_id = ?");
            params.add(Integer.parseInt(conditions.get("orderId").toString()));
        }
        
        // 处理订单编号
        if (conditions.containsKey("orderNumber")) {
            sqlBuilder.append(" AND order_number LIKE ?");
            params.add("%" + conditions.get("orderNumber") + "%");
        }

        // 处理客户ID
        if (conditions.containsKey("customerId")) {
            sqlBuilder.append(" AND customer_id = ?");
            params.add(Integer.parseInt(conditions.get("customerId").toString()));
        }
        
        // 处理仓库ID
        if (conditions.containsKey("warehouseId")) {
            sqlBuilder.append(" AND warehouse_id = ?");
            params.add(Integer.parseInt(conditions.get("warehouseId").toString()));
        }

        // 处理订单日期范围
        if (conditions.containsKey("orderDateFrom")) {
            sqlBuilder.append(" AND order_date >= ?");
            LocalDate fromDate = LocalDate.parse(conditions.get("orderDateFrom").toString(), DATE_FORMATTER);
            params.add(Timestamp.valueOf(fromDate.atStartOfDay()));
        }

        if (conditions.containsKey("orderDateTo")) {
            sqlBuilder.append(" AND order_date <= ?");
            LocalDate toDate = LocalDate.parse(conditions.get("orderDateTo").toString(), DATE_FORMATTER);
            params.add(Timestamp.valueOf(toDate.plusDays(1).atStartOfDay().minusNanos(1)));
        }
        
        // 处理订单状态
        if (conditions.containsKey("orderStatus")) {
            sqlBuilder.append(" AND order_status = ?");
            params.add(conditions.get("orderStatus").toString());
        }
        
        // 处理支付状态
        if (conditions.containsKey("paymentStatus")) {
            sqlBuilder.append(" AND payment_status = ?");
            params.add(conditions.get("paymentStatus").toString());
        }
        
        // 处理金额范围
        if (conditions.containsKey("minAmount")) {
            sqlBuilder.append(" AND total_amount >= ?");
            params.add(new BigDecimal(conditions.get("minAmount").toString()));
        }

        if (conditions.containsKey("maxAmount")) {
            sqlBuilder.append(" AND total_amount <= ?");
            params.add(new BigDecimal(conditions.get("maxAmount").toString()));
        }
        
        // 处理关键字查询（订单编号或备注）
        if (conditions.containsKey("keyword")) {
            sqlBuilder.append(" AND (order_number LIKE ? OR notes LIKE ?)");
            String keyword = "%" + conditions.get("keyword") + "%";
            params.add(keyword);
            params.add(keyword);
        }

        // 添加排序
        sqlBuilder.append(" ORDER BY order_id DESC");

        // 执行查询
        return executeQueryWithParams(sqlBuilder.toString(), params);
    }

    /**
     * 执行带参数的查询
     * @param sql SQL语句
     * @param params 查询参数列表
     * @return 销售订单列表
     */
    private static List<SalesOrders> executeQueryWithParams(String sql, List<Object> params) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<SalesOrders> orderList = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            
            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(i + 1, params.get(i));
            }
            
            resultSet = preparedStatement.executeQuery();
            orderList = extractOrdersFromResultSet(resultSet);
            
        } catch (SQLException e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "查询销售订单失败: " + e.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
        
        return orderList;
    }

    /**
     * 执行查询并返回结果列表
     * @param sql SQL查询语句
     * @param preparer SQL参数设置器
     * @return 销售订单列表
     */
    private static List<SalesOrders> executeQuery(String sql, PreparedStatementPreparer preparer) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<SalesOrders> orderList = new ArrayList<>();

        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            
            // 如果有参数需要设置
            if (preparer != null) {
                preparer.prepare(preparedStatement);
            }
            
            resultSet = preparedStatement.executeQuery();
            orderList = extractOrdersFromResultSet(resultSet);
            
        } catch (SQLException e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "查询销售订单失败: " + e.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
        
        return orderList;
    }
    
    /**
     * 从结果集中提取销售订单列表
     * @param resultSet 结果集
     * @return 销售订单列表
     * @throws SQLException SQL异常
     */
    private static List<SalesOrders> extractOrdersFromResultSet(ResultSet resultSet) throws SQLException {
        List<SalesOrders> orderList = new ArrayList<>();
        
        while (resultSet.next()) {
            SalesOrders order = new SalesOrders();
            order.setOrderId(resultSet.getInt("order_id"));
            order.setOrderNumber(resultSet.getString("order_number"));
            order.setCustomerId(resultSet.getInt("customer_id"));
            
            // 设置日期时间
            Timestamp orderDate = resultSet.getTimestamp("order_date");
            if (orderDate != null) {
                order.setOrderDate(orderDate.toLocalDateTime());
            }
            
            Date expectedShipDate = resultSet.getDate("expected_ship_date");
            if (expectedShipDate != null) {
                order.setExpectedShipDate(expectedShipDate.toLocalDate());
            }
            
            Date actualShipDate = resultSet.getDate("actual_ship_date");
            if (actualShipDate != null) {
                order.setActualShipDate(actualShipDate.toLocalDate());
            }
            
            order.setWarehouseId(resultSet.getInt("warehouse_id"));
            order.setOrderStatus(resultSet.getString("order_status"));
            order.setPaymentStatus(resultSet.getString("payment_status"));
            order.setSubtotal(resultSet.getBigDecimal("subtotal"));
            order.setTaxAmount(resultSet.getBigDecimal("tax_amount"));
            order.setDiscountAmount(resultSet.getBigDecimal("discount_amount"));
            order.setTotalAmount(resultSet.getBigDecimal("total_amount"));
            order.setPaidAmount(resultSet.getBigDecimal("paid_amount"));
            
            // 获取备注字段
            order.setNotes(resultSet.getString("notes"));
            
            // 设置时间戳
            Timestamp createdTime = resultSet.getTimestamp("created_time");
            if (createdTime != null) {
                order.setCreatedTime(createdTime.toLocalDateTime());
            }
            
            Timestamp updatedTime = resultSet.getTimestamp("updated_time");
            if (updatedTime != null) {
                order.setUpdatedTime(updatedTime.toLocalDateTime());
            }
            
            orderList.add(order);
        }
        
        return orderList;
    }
    
    /**
     * PreparedStatement参数设置器接口
     */
    @FunctionalInterface
    private interface PreparedStatementPreparer {
        void prepare(PreparedStatement preparedStatement) throws SQLException;
    }
    
    /**
     * 查询条件类
     */
    public static class QueryCriteria {
        private Integer orderId;
        private String orderNumber;
        private Integer customerId;
        private LocalDate startDate;
        private LocalDate endDate;
        private String orderStatus;
        private String paymentStatus;
        private BigDecimal minAmount;
        private BigDecimal maxAmount;
        private Integer warehouseId;
        private String keyword;
        
        // Getters and Setters
        public Integer getOrderId() { return orderId; }
        public void setOrderId(Integer orderId) { this.orderId = orderId; }
        
        public String getOrderNumber() { return orderNumber; }
        public void setOrderNumber(String orderNumber) { this.orderNumber = orderNumber; }
        
        public Integer getCustomerId() { return customerId; }
        public void setCustomerId(Integer customerId) { this.customerId = customerId; }
        
        public LocalDate getStartDate() { return startDate; }
        public void setStartDate(LocalDate startDate) { this.startDate = startDate; }
        
        public LocalDate getEndDate() { return endDate; }
        public void setEndDate(LocalDate endDate) { this.endDate = endDate; }
        
        public String getOrderStatus() { return orderStatus; }
        public void setOrderStatus(String orderStatus) { this.orderStatus = orderStatus; }
        
        public String getPaymentStatus() { return paymentStatus; }
        public void setPaymentStatus(String paymentStatus) { this.paymentStatus = paymentStatus; }
        
        public BigDecimal getMinAmount() { return minAmount; }
        public void setMinAmount(BigDecimal minAmount) { this.minAmount = minAmount; }
        
        public BigDecimal getMaxAmount() { return maxAmount; }
        public void setMaxAmount(BigDecimal maxAmount) { this.maxAmount = maxAmount; }
        
        public Integer getWarehouseId() { return warehouseId; }
        public void setWarehouseId(Integer warehouseId) { this.warehouseId = warehouseId; }
        
        public String getKeyword() { return keyword; }
        public void setKeyword(String keyword) { this.keyword = keyword; }
    }
    
    /**
     * 使用查询条件对象进行高级查询
     * @param criteria 查询条件对象
     * @return 销售订单列表
     */
    public static List<SalesOrders> advancedQuery(QueryCriteria criteria) {
        searchMode = true;
        StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM sales_orders WHERE 1=1");
        List<Object> params = new ArrayList<>();

        // 添加订单ID条件
        if (criteria.getOrderId() != null) {
            sqlBuilder.append(" AND order_id = ?");
            params.add(criteria.getOrderId());
        }

        // 添加订单编号条件
        if (criteria.getOrderNumber() != null && !criteria.getOrderNumber().isEmpty()) {
            sqlBuilder.append(" AND order_number LIKE ?");
            params.add("%" + criteria.getOrderNumber() + "%");
        }

        // 添加客户ID条件
        if (criteria.getCustomerId() != null) {
            sqlBuilder.append(" AND customer_id = ?");
            params.add(criteria.getCustomerId());
        }

        // 添加订单日期范围条件
        if (criteria.getStartDate() != null) {
            sqlBuilder.append(" AND order_date >= ?");
            params.add(Timestamp.valueOf(criteria.getStartDate().atStartOfDay()));
        }

        if (criteria.getEndDate() != null) {
            sqlBuilder.append(" AND order_date <= ?");
            params.add(Timestamp.valueOf(criteria.getEndDate().plusDays(1).atStartOfDay().minusNanos(1)));
        }

        // 添加订单状态条件
        if (criteria.getOrderStatus() != null && !criteria.getOrderStatus().isEmpty()) {
            sqlBuilder.append(" AND order_status = ?");
            params.add(criteria.getOrderStatus());
        }

        // 添加支付状态条件
        if (criteria.getPaymentStatus() != null && !criteria.getPaymentStatus().isEmpty()) {
            sqlBuilder.append(" AND payment_status = ?");
            params.add(criteria.getPaymentStatus());
        }

        // 添加金额范围条件
        if (criteria.getMinAmount() != null) {
            sqlBuilder.append(" AND total_amount >= ?");
            params.add(criteria.getMinAmount());
        }

        if (criteria.getMaxAmount() != null) {
            sqlBuilder.append(" AND total_amount <= ?");
            params.add(criteria.getMaxAmount());
        }

        // 添加仓库ID条件
        if (criteria.getWarehouseId() != null) {
            sqlBuilder.append(" AND warehouse_id = ?");
            params.add(criteria.getWarehouseId());
        }

        // 添加关键字搜索条件（搜索备注字段）
        if (criteria.getKeyword() != null && !criteria.getKeyword().isEmpty()) {
            sqlBuilder.append(" AND (order_number LIKE ? OR notes LIKE ?)");
            params.add("%" + criteria.getKeyword() + "%");
            params.add("%" + criteria.getKeyword() + "%");
        }

        // 添加排序
        sqlBuilder.append(" ORDER BY order_id DESC");

        return executeQueryWithParams(sqlBuilder.toString(), params);
    }
}